使用新字段插入数据或使用UpdateOne MongoDB进行条件更新 |
您所在的位置：网站首页 › mongodb ifnull › 使用新字段插入数据或使用UpdateOne MongoDB进行条件更新 |
|
百度翻译此文
有道翻译此文
问题描述
I have a document with format like this: { "f1": "v1", "f2": { "id": 1, "sub": "subv", "updatedAt": 123 } }I have an another source that give me a inputf2 object. I want to write an upsert query to find document with matching filter {"f2.id": inputf2.id} . I̶f̶ ̶f̶o̶u̶n̶d̶ ̶a̶n̶d̶ ̶i̶f̶ ̶f̶2̶.̶u̶p̶d̶a̶t̶e̶d̶A̶t̶ ̶ update whole f2 to inputf2. If found the id AND f2.updatedAt >= inputf2.updatedAt => do nothing (no update f2, no create new document). If not found the id => create new document with new field f1 (v1 is a hardcoded value) Example: Input: {"id": 1,"sub": "newsubv","updatedAt": 100}Because 100 < 123. id = 1 will not be updated. Output: { "f1": "v1", "f2": {"id": 1,"sub": "subv","updatedAt": 123} }Input: {"id": 1,"sub": "newsubv","updatedAt": 150}Because 150 > 123. id = 1 will be updated. Output: { "f1": "v1", "f2": {"id": 1,"sub": "newsubv","updatedAt": 150} }Input: {"id": 2,"sub": "newsubv","updatedAt": 100}Because input id = 2 is not found in db. It will be inserted whatever updatedAt is. Output: { "f1": "v1", "f2": {"id": 1,"sub": "subv","updatedAt": 123} }, { "f1": "v1", "f2": {"id": 2,"sub": "newsubv","updatedAt": 100} }I tried with both 2 types of update, update document or update aggregattion pipeline, but seem that any of them fit my requirement. With update document I can use $setOnInsert but can't set f2 with f2.updatedAt < inputf2.updatedAt condition: db.collection.update({ "f2.id": "1" }, { "$set": { // Can not check updatedAt = 100 < 123 and then do nothing "f2": { "id": 1 "sub": "newsubv", "updatedAt": 100 } }, "$setOnInsert": { "f1": "v1" } }, {"upsert": true});With update aggregation pipeline I can update f2 with f2.updatedAt < inputf2.updatedAt condition with a bit tricky by cloning f2 to new field oldf2, then apply the condition and remove oldf2. But in aggregation, there is no $setOnItem function: db.test.updateOne( {"f2.id": 1}, [ {$set: {"oldf2": "$f2"}}, { $set: { "f2": { $cond: [{$lt: ["$f2.updatedAt", 100]}, { "id": 1, "sub": "newsubv", "updatedAt": 100 }, "$oldf2"] } } }, {$set: {"oldf2": "$$REMOVE"}}, // How to apply something like $setOnInsert function? ], {"upsert":true})I must use updateOne because there is a list of f2 item and I want to batch update these items in bulkWrite. Noted: f2.id is an unique field in collection Can anyone help me to write query for this logic? Thank you guys very much. 推荐答案Finally, I can write the query. Here is my solution: db.test.updateOne( {"f2.id": 1}, [{ $set: { "f2": { $cond: [ {$lt: ["$f2.updatedAt", 100]}, // time check condition {"id": 1,"sub": "newsubv","updatedAt": 100}, // overwrite value "$f2" // no change, return origin value ]}, "f1": {$ifNull: ["$f1", "v1"]}, // this can do the same as $setOnInsert } }], {"upsert":true} )Testcase: Input 1: Mongo Playground {"id": 1,"sub": "newsubv","updatedAt": 100}Input 2: Mongo Playground {"id": 1,"sub": "newsubv","updatedAt": 200}Input 3: Mongo Playground {"id": 2,"sub": "newsubv","updatedAt": 100} 其他推荐答案You can use the first method almost as you did, just with a condition. Edit: But if you want to change f2.id in case of insert, you should separate the f2, like this: db.collection.update({ "f2.id": 1, "f2.updatedAt": { $lte: inputf2.updatedAt // Here you can check updatedAt = 100 < 123 and then stop updating } }, { "$set": { "f2.sub": "newsubv", "f2.updatedAt": 100 }, "$setOnInsert": { "f1": "v1", "f2.id": newF2, } }, { "upsert": true })You can see it on the playground And you can play with the inputf2.updatedAt to be 100 or 150 to see both cases that I pasted here: If f2.updatedAt >= inputf2.updatedAt, we will get two different documents on the db. The new one will have inputf2.updatedAt, but with new id, as this should be a unique id: [ { "_id": ObjectId("5a934e000102030405000000"), "f1": "v1", "f2": { "id": 1, "sub": "subv", "updatedAt": 123 } }, { "_id": ObjectId("625b1873cdbb97ce928d8898"), "f1": "v1", "f2": { "id": 2, "sub": "newsubv", "updatedAt": 100 } } ]Otherwise, f2.updatedAt < inputf2.updatedAt : the document on the db will be updated, but will keep its original id: { "_id": ObjectId("5a934e000102030405000000"), "f1": "v1", "f2": { "id": 1, "sub": "newsubv", "updatedAt": 150 } }BTW, there is no problem to use this method inside a bulk update |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |